/*
This file generates all descriptive evidence related to household lending
in the paper, including the appendix. In particular, it generates:

	- Figure 1.a - Nonbank Debt Share over Time
	- Figure E.1.a - Largest industries among nonbank lenders
	- Figure E.2 - Sectoral allocation of bank vs nonbank credit 
	- Figure E.3.a - Map of Nonbank Debt Share
	- Figure E.4.a Heterogeneity in borrowing patterns
	- Table 1.b - Summary statistics table (firms)
	
*/

* Load corporate lending dataset
use if delinquency != 1 using "${procdata}/URTEVIRK_FIRE_FIRM_03-18.dta", clear


////////////////////////////////////////
//// Nonbank Debt Share over Time  
//// Figure 1.a 

preserve 

gcollapse (sum) REST_GAELD_BLB, by(nonbank_lender year)

bysort year: egen agg_debt = total(REST_GAELD_BLB) 
bysort year: egen agg_nonbankdebt = total(REST_GAELD_BLB) if nonbank_lender == 1
bysort year: gen nonbank_share = agg_nonbankdebt*100/agg_debt

gcollapse (lastnm) nonbank_share, by(year)

qui su year

tw connected nonbank_share year, ///
	lw(thick) lc(black) mcolor(black) xtitle("")  ///
	ytitle("%", orient(horizontal) size(large)) ///
	yscale(range(0 12)) ylabel(0(2)12, labsize(large)) ///
	xlabel(`r(min)'(2)`r(max)', grid angle(0) labsize(large)) ///
	title("")  name(Fig1_a, replace) legend(off) ///
	
// Export results
graph export "${figures}/URTEVIRK_share_nonbankdebt.eps", ///
	replace fontface("Arial") mag(133)
	
restore 


////////////////////////////////////////
//// Figure E.1.a - Largest industries among nonbank lenders

preserve 

// Compute total credit by banks+nonbanks 
egen agg_debt = total(REST_GAELD_BLB)

// Keep only nonbanks, then compute the share of total credit given by each nonbank type
keep if nonbank_lender == 1
gcollapse (sum) REST_GAELD_BLB (last) agg_debt, by(JUR_HOVED_BRA_DB07_lender)
gen share_nonbank_debt = REST_GAELD_BLB*100/agg_debt


*plot top 3 nonbank types
gsort -share_nonbank_debt
generate counter=_n

graph hbar share_nonbank_debt if counter <= 3, ///
	over(JUR_HOVED_BRA_DB07_lender, sort(share_nonbank_debt) descending label(labsize(large))) ///
	bar(1, fcolor(gs10) lcolor(black) lwidth(0.3)) yla(, labsize(large)) /// 
	ytitle("") b1title("%", size(large)) title("") subtitle("") legend(off) ///
	scheme(s1mono) ysize(3) xsize(6) name(E1_a, replace)

// Export horizontal version
graph export "${figures}/FIRMS_chart_NonbankIndustries.eps", ///
	replace fontface("Arial") mag(133)

restore 


////////////////////////////////////////////////////////////////////////////////
/// Figure E.2 - Sectoral allocation of bank vs nonbank credit 

preserve 
keep if nonbank_lender != .
gcollapse (sum) REST_GAELD_BLB,  by(industry_borrower_coarse nonbank_lender) labelformat(#sourcelabel#)

bysort nonbank_lender: egen tot_debt_lendertype = total(REST_GAELD_BLB)
bysort nonbank_lender: gen debt_share = REST_GAELD_BLB*100 / tot_debt_lendertype 

// Reshape data to have bank and nonbank shares side by side
keep industry_borrower_coarse nonbank_lender debt_share
reshape wide debt_share, i(industry_borrower_coarse) j(nonbank_lender)

// Rename variables for clarity
rename debt_share0 bank_share
rename debt_share1 nonbank_share

// Handle missing values
replace bank_share = 0 if missing(bank_share)
replace nonbank_share = 0 if missing(nonbank_share)

graph hbar bank_share nonbank_share if bank_share > 1 | nonbank_share > 1, ///
	over(industry_borrower_coarse, sort(nonbank_share) label(labsize(3.5)) ) ///
	title("") ytitle("") b1title("%") yla(, labsize(large)) ///
	bar(1, fcolor(gs12) lcolor(black) lwidth(0.3)) ///
	bar(2, fcolor(gs6) lcolor(black) lwidth(0.3)) ///
	blabel(bar, format(%9.1f) size(2.5)) ysize(6) xsize(8) /// 
	legend(order(1 "Banks" 2 "Nonbanks") position(1) ring(0) cols(1) size(large) ///
	region(lcolor(black) fcolor(white) margin(small)) ///
		nobox region(lstyle(none) style(outline))) name(E2, replace)
	
graph export "${figures}/FIRMS_chart_BorrowerIndustries.eps", ///
	replace fontface("Arial") mag(133)

restore 


////////////////////////////////////////
// Figure E.3.a  - Map of Nonbank Debt Share (Region) 

preserve

* Sum credit by lender type in each municipality 
* Municipality is based on the borrower's HQ location
gcollapse (sum) REST_GAELD_BLB, by(JUR_BEL_KOM_KODE nonbank_lender)

bysort JUR_BEL_KOM_KODE: egen agg_debt = total(REST_GAELD_BLB) 
gen share_nonbank_debt = REST_GAELD_BLB / agg_debt if nonbank_lender == 1
keep if nonbank_lender == 1
drop REST_GAELD_BLB nonbank_lender agg_debt

* Cannot create map on remote DST server, export data underlying the map instead
export excel using "${tables}/FIRMS_chart_MuniNonbankShare", firstrow(varlabels) replace

restore 


////////////////////////////////////////////////////////////////////////////////
/// Figure E4.a Heterogeneity in borrowing patterns
	
preserve 
merge m:1 cvrnr_lender year using "$procdata/final_Experian.dta", nogen keep(match master) 
drop if mi(nonbank_lender)
	
	 
// Identify firms with bank and nonbank debt 
gegen aux_bank_credit = total(REST_GAELD_BLB) if nonbank_lender == 0, by(id_borrower year)
gegen aux_nonbank_credit = total(REST_GAELD_BLB) if nonbank_lender == 1, by(id_borrower year)
gegen tot_bank_credit = max(aux_bank_credit) , by(id_borrower year)
replace tot_bank_credit = 0 if mi(tot_bank_credit)
gegen tot_nonbank_credit = max(aux_nonbank_credit), by(id_borrower year)
replace tot_nonbank_credit = 0 if mi(tot_nonbank_credit)
gegen tot_firm_credit = total(REST_GAELD_BLB), by(id_borrower year)
gen nonbank_share = tot_nonbank_credit / tot_firm_credit
replace  nonbank_share = 0 if mi(nonbank_share)

gen co_borrower = 0 if nonbank_share == 1 | nonbank_share == 0
replace co_borrower = 1 if tot_bank_credit > 0 & tot_nonbank_credit > 0 & !mi(tot_bank_credit) & !mi(tot_nonbank_credit)
tab co_borrower

gen only_nonbank = (nonbank_share == 1) 

** Collapse to firm-year level 
gcollapse (max) co_borrower nonbank_share only_nonbank    ///
	(lastnm)  tot_firm_credit GF_OMS , by(id_borrower year)

* Generate deciles of firm size distribution measured via sales 
gquantiles Sales_deciles = GF_OMS, xtile n(10) by(year)

** Collapse to sales decile-level  
drop if tot_firm_credit == 0
replace co_borrower = 0 if mi(co_borrower)
gcollapse (mean) nonbank_share co_borrower only_nonbank, by(Sales_deciles)

replace nonbank_share = nonbank_share * 100
replace co_borrower = co_borrower * 100
replace only_nonbank = only_nonbank * 100

label define sales_lab 1 "P0-P10" 2 "P10-P20" 3 "P20-P30" 4 "P30-P40" 5 "P40-P50" 6 "P50-P60"  7 "P60-P70" 8 "P70-P80" 9 "P80-P90" 10 "P90-P100" , replace 
label values Sales_deciles sales_lab


graph bar co_borrower only_nonbank nonbank_share, ///
	over(Sales_deciles, lab(angle(45) labsize(medlarge))) /// labsize(2.8)
	title("") ysize(4) xsize(8) name(E4, replace) ///
	ytitle("%", height(4) size(large) orient(horizontal)) /// 3.0
	b1title("Sales percentiles", height(4) size(large)) /// size(3.0)
	ylabel(0(5)20, labsize(large) format(%9.0f) grid) /// labsize(2.8)
	legend(order(1 "Share of firms w. bank & nonbank credit" ///
		2 "Share of firms w. only nonbank credit" ///
		3 "Nonbank credit share") ///
		nobox position(11) ring(0) cols(1) size(3) ///
		region(lstyle(none) style(outline))) ///
	bar(1, fcolor(gs13) lcolor(black) lwidth(0.3)) ///
	bar(2, fcolor(gs8) lcolor(black) lwidth(0.3)) ///
	bar(3, fcolor(none) lcolor(black) lwidth(0.6)) 
	

graph export "${figures}/firm_coborrowing_Sales.eps", ///
	replace fontface("Arial") mag(133)
	
restore 


////////////////////////////////////////////////////////////////////////////////
/// Table 1.B  - Summary statistics table (firms)

bysort cvrnr_borrower year: egen tot_debt=total(REST_GAELD_BLB)
drop if tot_debt<=0		//drops firms with nonpositive debt outstanding
by cvrnr_borrower year: egen nonbank_volume=total(REST_GAELD_BLB) ///
	if nonbank_lender==1
by cvrnr_borrower year: gen nonbank_share=nonbank_volume/tot_debt
replace nonbank_share = 0 if nonbank_share == .
by cvrnr_borrower year: gen nonbank_borrower = 1 if nonbank_share>=0.5
replace nonbank_borrower = 0 if nonbank_borrower == .
label def nonbank_borrower 1 "Nonbank Borrower"  0 "Bank Borrower"


*compute some financial ratios 
gen debt_to_equity = (past-egul)/egul if egul>0 		
gen debt_mio_dkk = tot_debt / 1000000 
gen interest_mio_dkk = RNT_BLB / 1000000
gen sales_mio_dkk = GF_OMS / 1000000
gen AT_mio_dkk = GF_AT / 1000000

* Compute number of distinct lenders per firm-year
sort cvrnr_borrower year cvrnr_lender
by cvrnr_borrower year: gen cvrnr_lender2 = cvrnr_lender[_n-1] if year != 2003
by cvrnr_borrower year: egen lender_count = total(cond(cvrnr_lender == cvrnr_lender2,0,1))

by cvrnr_borrower year: gen cvrnr_nonbank = cvrnr_lender if nonbank_lender == 1 
by cvrnr_borrower year: gen cvrnr_nonbank2 = cvrnr_lender[_n-1] if nonbank_lender == 1 & year != 2003
by cvrnr_borrower year: egen nonbanklender_count = total(cond(cvrnr_nonbank == cvrnr_nonbank2,0,1))


*label variables
label variable debt_mio_dkk "Unsecured debt (m DKK)" // this used to be REST_GAELD_BLB
label variable interest_mio_dkk "Total interest payments (m DKK)"
label variable sales_mio_dkk "Sales revenue (m DKK)"
label variable AT_mio_dkk "Total assets (m DKK)"
label variable GF_AARSV "FTE employees"
label variable debt_to_equity "Debt to equity ratio"
label var GF_ANSATTE "Employees in Nov."
label var GF_AARSV "FTE employees"
label var lender_count "Number of lenders"
label var nonbanklender_count "Number of nonbank lenders"
label var intrate "Interest rate"
label var nonbank_share "Nonbank debt share"
label var borrower_age "Borrower age"

** Mark those observations in KM-sample
xtset id_borrowerlender year
local regspec inter1-inter5, noconstant a(id_borrower#year id_lender) vce(cluster id_lender#id_borrower)
qui reghdfe intrate `regspec'
qui gen sample_reg = 1 if e(sample)
qui reghdfe lndebt `regspec'
qui gen sample_reg_debt = 1 if e(sample)
gegen in_sample = max(sample_reg sample_reg_debt), by(id_borrower year)


keep if inrange(year,2004,2017)

local borrower_stats debt_mio_dkk intrate nonbank_share lender_count nonbanklender_count ///  
	borrower_age debt_to_equity  AT_mio_dkk GF_AARSV  

******* Collapse data first to firm-year level 
gcollapse (mean) `borrower_stats' nonbank_borrower in_sample, by(cvrnr_borrower year) labelformat(#sourcelabel#)
eststo clear
eststo full_sample: quietly estpost summarize `borrower_stats', d
eststo full_nonbanks: quietly estpost summarize `borrower_stats' if nonbank_borrower == 1, d
eststo full_banks: quietly estpost summarize `borrower_stats' if nonbank_borrower == 0, d

eststo insample: quietly estpost summarize `borrower_stats' if in_sample == 1, d	
eststo sample_nonbanks: quietly estpost summarize `borrower_stats' if (nonbank_borrower == 1) & (in_sample == 1), d
eststo sample_banks: quietly estpost summarize `borrower_stats' if (nonbank_borrower == 0) & (in_sample == 1), d


esttab full_sample full_nonbanks full_banks, cells("count(fmt(%12.0gc)) mean(fmt(%6.2f)) sd(fmt(%8.2fc)) p50(fmt(%6.2f))") mtitles noobs

esttab insample sample_nonbanks sample_banks, cells("count(fmt(%12.0gc)) mean(fmt(%6.2f)) sd(fmt(%8.2fc)) p50(fmt(%6.2f))") mtitles noobs


local sharedopts 

esttab full_sample insample, mgroups("Full dataset" "Borrowers with bank and nonbank lenders", pattern(1 1 ) span) ///
		nonumbers noobs nomtitles ///
		cells("count(fmt(%12.0gc)) mean(fmt(%6.2f)) sd(fmt(%8.2fc)) p50(fmt(%6.2f))") 


esttab full_sample insample using "$tables/Consolidated_DescStat_Borrowers.tex", ///
		posthead("\hline \\ \multicolumn{4}{l}{\textbf{Panel B. Firms}} \\\\[-1ex]") ///
		fragment append nomtitles nonumbers nolines noobs ///
		cells("count(fmt(%12.0gc)) mean(fmt(%6.2f)) sd(fmt(%8.2fc)) p50(fmt(%6.2f))") ///
		collabels(none) ///
		prefoot("\hline") ///
		postfoot("\hline\hline \end{tabular}") label 



		



